/**
 * Copyright (c) 2017,安徽皖通科技股份有限公司 All rights reserved.
 *
 * @data:2017年10月10日
 * @author:MaShibo
 */
package com.wttech.dzzf.process.dao;

import cn.com.taiji.sdk.model.comm.protocol.AbstractSdtsRequest;
import cn.com.taiji.sdk.model.comm.protocol.backlist.CardBlackListUploadRequest;
import cn.com.taiji.sdk.model.comm.protocol.backlist.OBUBlackListUploadRequest;
import cn.com.taiji.sdk.model.comm.protocol.user.CardUploadRequest;
import cn.com.taiji.sdk.model.comm.protocol.user.OBUUploadRequest;
import cn.com.taiji.sdk.model.comm.protocol.user.UserUploadRequest;
import cn.com.taiji.sdk.model.comm.protocol.user.VehicleUploadRequest;
import com.wtkj.dao.SqlBuilder;
import com.wtkj.dao.impl.*;
import com.wtkj.vo.AbstractValueObject;
import com.wtkj.vo.dzzf.base.kfwd.CardInfo;
import com.wtkj.vo.dzzf.base.kfwd.FundAccount;
import com.wtkj.vo.dzzf.base.kfwd.OBU;
import com.wtkj.vo.dzzf.base.kfwd.VehicleInfo;
import com.wttech.dzzf.process.constant.ExecuteTable;
import com.wttech.dzzf.process.model.Organization;
import com.wttech.dzzf.process.model.UploadRsp;
import com.wttech.dzzf.process.model.user.User;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import org.springframework.util.CollectionUtils;

import javax.annotation.Resource;
import java.math.BigDecimal;
import java.util.List;

/**
 * 转换客户相关信息
 *
 * @author MaShibo
 * @date 2017年10月10日
 */
@Repository
public class TransferUserInfoDao extends BaseDataTemplate {


	/**
	 * 查询数量SQL
	 */
	private static final String QUERY_USERINFO_COUNT = "SELECT COUNT(1) FROM %s WHERE UPLOADFLAG IS NULL";

	private static final String UPDATE_USERINFO_UPLOAD = "UPDATE %s SET UPLOADFLAG = ? WHERE ID =? ";

	/**
	 * 查询记录SQL
	 */
	private static final String QUERY_USERINFO_RECORD = "SELECT * " + "  FROM (SELECT A.*, ROWNUM AS RM FROM %s A WHERE UPLOADFLAG IS NULL ) "
			+ " WHERE RM >= ? " + "   AND RM <= ?";

	@Resource(name = "dzzfJdbcTemplate")
	private JdbcTemplate dzzfJdbcTemplate;

	@Resource(name = "jdbcTemplate")
	private JdbcTemplate testJdbcTemplate;

	public Integer queryNormalAccountNum() {
		String sql = " SELECT COUNT(1) FROM ( " +
				buildQueryAccountSql(true) +
				") ";
		return dzzfJdbcTemplate.queryForObject(sql, Integer.class);
	}

	@SuppressWarnings("unchecked")
	public List<FundAccount> queryNormalAccountInfos(int begin, int end) {
		String sql = " SELECT * FROM ( " +
				buildQueryAccountSql(true) +
				") " +
				" WHERE RM >= ? " +
				" AND RM <= ? ";
		return (List<FundAccount>) dzzfJdbcTemplate.query(sql, new Object[]{begin, end},
				new ListResultSetExtractor(new FundAccount()));
	}

	private String buildQueryAccountSql(boolean isNormal) {
		return
				"SELECT M.*, ROWNUM AS RM " +
						"  FROM (SELECT A.*, " +
						"               COUNT(1) OVER(PARTITION BY A.CERTIFINO, A.CERTIFITYPE) AS RN, " +
						"               J.CHANNELID " +
						"          FROM AC_FUNDACCOUNT A " +
						"          LEFT JOIN JG_ORGANIZATION J " +
						"            ON A.CLIENTNO = J.CODE " +
						"         WHERE A.ACCOUNTSTATUS = 1 " +
						"           AND EXISTS (SELECT 1 " +
						"                  FROM AC_ACCOUNT T " +
						"                  JOIN AC_CARDINFO C " +
						"                    ON T.ACCOUNTID = C.ACCOUNTID " +
						"                 WHERE T.FUNDACCOUNTID = A.FUNDACCOUNTID " +
						"                   AND C.ECARDSTATUS IN (1, 2, 3) AND USERID IS NULL) " +
						"         ORDER BY A.OPTON DESC) M" +
						(isNormal ? " WHERE RN=1 " : " WHERE RN > 1 ");
	}

	public Integer queryAccountNumV2(boolean isNormal) {
		String sql = " SELECT COUNT(1) FROM ( " +
				buildQueryAccountSql(isNormal) +
				") ";
		return dzzfJdbcTemplate.queryForObject(sql, Integer.class);
	}

	/**
	 * 测试数据库查询资金账户信息
	 *
	 * @param begin 起始行
	 * @param end   结束行
	 * @return 资金账户信息
	 */
	@SuppressWarnings("unchecked")
	public List<FundAccount> queryNormalAccountInfosV2(int begin, int end) {
		String sql = " SELECT * FROM ( " +
				buildQueryAccountSql(true) +
				") " +
				" WHERE RM >= ? " +
				" AND RM <= ? ";
		return (List<FundAccount>) dzzfJdbcTemplate.query(sql, new Object[]{begin, end},
				new ListResultSetExtractor(new FundAccount()));
	}

	private String buildQueryAccountSqlV2(boolean isNormal) {
		StringBuilder sql = new StringBuilder();
		sql.append(" SELECT M.*, ROWNUM AS RM ");
		sql.append("   FROM (SELECT A.*, ");
		sql.append("                COUNT(1) OVER(PARTITION BY A.CERTIFINO, A.CERTIFITYPE) AS RN ");
		sql.append("           FROM AC_FUNDACCOUNT A ");
		sql.append("          WHERE A.ACCOUNTSTATUS = 1) M ");
		sql.append(isNormal ? " WHERE RN=1 " : " WHERE RN > 1 ");
		return sql.toString();
	}

	@SuppressWarnings("unchecked")
	public List<FundAccount> queryAbnormalAccountInfos() {
		StringBuilder sql = new StringBuilder();
		sql.append(" SELECT * FROM ( ");
		sql.append(buildQueryAccountSql(false));
		sql.append(") ");
		return (List<FundAccount>) dzzfJdbcTemplate.query(sql.toString(), new ListResultSetExtractor(new FundAccount()));
	}

	public Integer queryUserSeqByYmd(String ymd) {
		final String sql = "SELECT NVL(Q.NUM, 1) FROM BS_USERSEQ Q WHERE Q.YMD=? ";
		return testJdbcTemplate.queryForObject(sql, Integer.class, ymd);
	}

	public String queryChannelIdByClientNo(String clientNo) {
		final String sql = "SELECT J.CHANNELID FROM JG_ORGANIZATION J WHERE J.CODE=? ";
		return testJdbcTemplate.queryForObject(sql, String.class, clientNo);
	}

	@SuppressWarnings("unchecked")
	public List<CardInfo> queryCardInfosByFundAccount(String fundAccountId) {
		StringBuilder sql = new StringBuilder();
		sql.append(" SELECT C.* ");
		sql.append("   FROM AC_CARDINFO C ");
		sql.append("   JOIN AC_ACCOUNT T ");
		sql.append("     ON C.ACCOUNTID = T.ACCOUNTID ");
		sql.append("  WHERE T.FUNDACCOUNTID = ? ");
//        sql.append("    AND C.ECARDSTATUS IN (1, 2, 3) ");
		sql.append(" ORDER BY C.OPTON DESC ");
		return (List<CardInfo>) dzzfJdbcTemplate.query(sql.toString(), new Object[]{fundAccountId},
				new ListResultSetExtractor(new CardInfo()));
	}

	@SuppressWarnings("unchecked")
	public VehicleInfo queryDzzfVehicleInfo(String vehPlate, Integer vehColor) {
		final String sql = "SELECT * FROM AC_VEHICLEINFO A WHERE A.VEHPLATE = ? AND A.VEHCOLOR = ?";
		List<VehicleInfo> vehicleInfos = (List<VehicleInfo>) dzzfJdbcTemplate.query(sql, new ListResultSetExtractor(new VehicleInfo()), vehPlate, vehColor);
		return CollectionUtils.isEmpty(vehicleInfos) ? null : vehicleInfos.get(0);
	}

	@SuppressWarnings("unchecked")
	public VehicleInfo queryTestVehicleInfo(String vehPlate, Integer vehColor) {
		final String sql = "SELECT * FROM AC_VEHICLEINFO A WHERE A.VEHPLATE = ? AND A.VEHCOLOR = ?";
		List<VehicleInfo> vehicleInfos = (List<VehicleInfo>) testJdbcTemplate.query(sql, new ListResultSetExtractor(new VehicleInfo()), vehPlate, vehColor);
		return CollectionUtils.isEmpty(vehicleInfos) ? null : vehicleInfos.get(0);
	}

	public Organization queryOrganizationByNo(String clientNo) {
		Organization organization = new Organization();
		organization.setCode(clientNo);
		return (Organization) queryTestObject(organization);
	}

	@SuppressWarnings("unchecked")
	public OBU queryOBUByCardInfoId(String cardInfoId) {
		final String sql =
				"SELECT A.*, J.CHANNELID " +
						"  FROM AC_OBU A " +
						"  LEFT JOIN JG_ORGANIZATION J " +
						"    ON A.CLIENTNO = J.CODE " +
						" WHERE A.CARDINFOID = ?";
		List<OBU> obus = (List<OBU>) dzzfJdbcTemplate.query(sql, new ListResultSetExtractor(new OBU()), cardInfoId);
		return CollectionUtils.isEmpty(obus) ? null : obus.get(0);
	}

	@SuppressWarnings("unchecked")
	public List<OBU> queryOBUbyVeh(String vehPlate, Integer vehColor) {
		String sql =
				"SELECT O.*, G.BRAND, G.MODEL, J.CHANNELID " +
						"  FROM AC_OBU O " +
						"  LEFT JOIN KC_OBUGOODS G " +
						"    ON O.OBUNO = G.OBUNO " +
						"  LEFT JOIN JG_ORGANIZATION J " +
						"    ON O.CLIENTNO = J.CODE " +
						" WHERE O.VEHICLEPLATE = ? " +
						"   AND O.VEHICLECOLOR = ? " +
						" ORDER BY O.OPTON DESC";
		return (List<OBU>) dzzfJdbcTemplate.query(sql, new Object[]{vehPlate, vehColor},
				new ListResultSetExtractor(new OBU()));
	}

	@SuppressWarnings("unchecked")
	public void createDzzfObjcet(AbstractValueObject obj) {
		ValueObjectCreateSqlBuilder builder = new ValueObjectCreateSqlBuilder(obj);
		dzzfJdbcTemplate.execute(builder.getSqlString(), new ValueObjectCreateCallback(builder));
	}


	@SuppressWarnings("unchecked")
	public void createTestObjcet(AbstractValueObject obj) {
		ValueObjectCreateSqlBuilder builder = new ValueObjectCreateSqlBuilder(obj);
		testJdbcTemplate.execute(builder.getSqlString(), new ValueObjectCreateCallback(builder));
	}

	public void updateTestObject(AbstractValueObject obj) {
		ValueObjectUpdateSqlBuilder builder = new ValueObjectUpdateSqlBuilder(obj);
		testJdbcTemplate.update(builder.getSqlString(), builder.getParameters());
	}

	@SuppressWarnings("unchecked")
	public AbstractValueObject queryTestObject(AbstractValueObject obj) {
		SqlBuilder sqlBuilder = new ValueObjectSqlBuilder(obj);
		return (AbstractValueObject) testJdbcTemplate.query(sqlBuilder.getSqlString(), sqlBuilder.getParameters(),
				new ValueObjectResultSetExtractor(obj));
	}

	@SuppressWarnings("unchecked")
	public AbstractValueObject queryDzzfObject(AbstractValueObject obj) {
		SqlBuilder sqlBuilder = new ValueObjectSqlBuilder(obj);
		return (AbstractValueObject) dzzfJdbcTemplate.query(sqlBuilder.getSqlString(), sqlBuilder.getParameters(),
				new ValueObjectResultSetExtractor(obj));
	}

	public int updateDzzfObject(AbstractValueObject valueObject) {
		ValueObjectUpdateSqlBuilder builder = new ValueObjectUpdateSqlBuilder(
				valueObject);
		return dzzfJdbcTemplate.update(builder.getSqlString(), builder
				.getParameters());
	}

	@SuppressWarnings("unchecked")
	public List<? extends AbstractValueObject> queryDzzfList(
			AbstractValueObject parameterValueObject) throws IllegalAccessException, InstantiationException {
		SqlBuilder sqlBuilder = new ValueObjectSqlBuilder(
				parameterValueObject);
		AbstractValueObject valueObject = parameterValueObject.getClass()
				.newInstance();
		return (List<? extends AbstractValueObject>) dzzfJdbcTemplate.query(
				sqlBuilder.getSqlString(), sqlBuilder.getParameters(),
				new ListResultSetExtractor(valueObject));
	}


	// ~ 查询客户、车辆、卡、OBU信息
	// ========================================================================

	public List<UserUploadRequest> queryUserReqInfos(int startRow, int endRow) {
		return testJdbcTemplate.query(String.format(QUERY_USERINFO_RECORD, "BS_USER"),
				new Object[]{startRow, endRow}, BeanPropertyRowMapper.newInstance(UserUploadRequest.class));
	}

	public List<VehicleUploadRequest> queryVehicleReqInfos(int startRow, int endRow) {
		return testJdbcTemplate.query(String.format(QUERY_USERINFO_RECORD, "BS_VEHICLE"),
				new Object[]{startRow, endRow}, BeanPropertyRowMapper.newInstance(VehicleUploadRequest.class));
	}

	public List<CardUploadRequest> queryCardReqInfos(int startRow, int endRow) {
		return testJdbcTemplate.query(String.format(QUERY_USERINFO_RECORD, "BS_CARD"),
				new Object[]{startRow, endRow}, BeanPropertyRowMapper.newInstance(CardUploadRequest.class));
	}

	public List<OBUUploadRequest> queryObuReqInfos(int startRow, int endRow) {
		return testJdbcTemplate.query(String.format(QUERY_USERINFO_RECORD, "BS_OBU"), new Object[]{startRow, endRow},
				BeanPropertyRowMapper.newInstance(OBUUploadRequest.class));
	}

	public List<? extends AbstractSdtsRequest> queryUseriInfoRecord(String tableName, Class<? extends AbstractSdtsRequest> requireType,
																	int startRow, int endRow) {
		return testJdbcTemplate.query(String.format(QUERY_USERINFO_RECORD, tableName),
				new Object[]{startRow, endRow}, BeanPropertyRowMapper.newInstance(requireType));
	}

	public List<? extends AbstractSdtsRequest> queryCardBlacklist(int startRow, int endRow){
		final String sql =
				"SELECT *\n" +
						"  FROM (SELECT A.ISSUERID || ',' || A.ID AS ISSUERID,\n" +
						"               A.CREATIONTIME,\n" +
						"               A.TYPE,\n" +
						"               A.CARDID,\n" +
						"               A.STATUS,\n" +
						"               ROWNUM AS RM\n" +
						"          FROM BS_CARDBLACKLIST A\n" +
						"         WHERE A.UPLOADFLAG IS NULL)\n" +
						" WHERE RM >= ?\n" +
						"   AND RM <= ?";
		return testJdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(CardBlackListUploadRequest.class), startRow, endRow);
	}

	public List<? extends AbstractSdtsRequest> queryObuBlacklist(int startRow, int endRow){
		final String sql =
				"SELECT *\n" +
						"  FROM (SELECT A.ISSUERID || ',' || A.ID AS ISSUERID,\n" +
						"               A.CREATIONTIME,\n" +
						"               A.TYPE,\n" +
						"               A.OBUID,\n" +
						"               A.STATUS,\n" +
						"               ROWNUM AS RM\n" +
						"          FROM BS_OBUBLACKLIST A\n" +
						"         WHERE A.UPLOADFLAG IS NULL)\n" +
						" WHERE RM >= ?\n" +
						"   AND RM <= ?";
		return testJdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(OBUBlackListUploadRequest.class), startRow, endRow);
	}

	// ~ 查询客户、车辆、卡、OBU信息数量
	// =================================================

	public Integer queryUserInfoCount(String tableName) {
		return testJdbcTemplate.queryForObject(String.format(QUERY_USERINFO_COUNT, tableName), Integer.class);
	}

	// ~查询卡片余额信息
	//  =================================================

	public BigDecimal queryCardBalance(String accountId) {
		final String sql = "SELECT A.ACCOUNTBALANCE FROM AC_ACCOUNT A WHERE A.ACCOUNTID = ? ";
		return dzzfJdbcTemplate.queryForObject(sql, BigDecimal.class, accountId);
	}

	public Integer queryCountByFundAccountId(String fundAccountId) {
		final String sql = "SELECT COUNT(1) FROM BS_PROCESS A WHERE A.FUNDACCOUNTID = ? ";
		return testJdbcTemplate.queryForObject(sql, Integer.class, fundAccountId);
	}

	public int insertProcessFlag(String fundAccountId) {
		final String sql = "INSERT INTO BS_PROCESS (FUNDACCOUNTID) VALUES (?) ";
		return testJdbcTemplate.update(sql, String.class, fundAccountId);
	}

	//~ 记录上传错误的信息
	// ======================================================


	public int updateUploadFailture(String id, String tableName) {
		final String sql = "UPDATE  %s A SET A.OPERATION =5  WHERE A.ID = ? ";
		return testJdbcTemplate.update(String.format(sql, tableName), id);
	}

	@SuppressWarnings("uncheck")
	public List<CardInfo> queryCardInfosByVehInfo(String vehPlate, Integer vehColor) {
		return (List<CardInfo>) dzzfJdbcTemplate.query("SELECT * FROM AC_CARDINFO A WHERE A.ECARDSTATUS IN (1, 2, 3) AND A.VEHICLEPLATE=? AND A.VEHICLECOLOR=? ORDER BY A.OPTON DESC", new ListResultSetExtractor(new CardInfo()), vehPlate, vehColor);
	}


	public int queryUserInfoNum(String id, String tableName) {
		final String sql = "SELECT COUNT(1) FROM %s WHERE ID=? ";
		return testJdbcTemplate.queryForObject(String.format(sql, tableName), Integer.class, id);
	}

	public int queryCardUploadInfoById(String cardId) {
		final String sql = "SELECT COUNT(1) FROM BS_CARD A WHERE A.ID = ?";
		return testJdbcTemplate.queryForObject(sql, Integer.class, cardId);
	}

	public FundAccount queryFundAccountByEcardNo(String ecardNo) {
		final String sql =
				"SELECT F.* " +
						"  FROM AC_CARDINFO A " +
						"  JOIN AC_ACCOUNT T " +
						"    ON A.ACCOUNTID = T.ACCOUNTID " +
						"  JOIN AC_FUNDACCOUNT F " +
						"    ON T.FUNDACCOUNTID = F.FUNDACCOUNTID " +
						" WHERE A.ECARDNO = ?";
		@SuppressWarnings("unchecked")
		List<FundAccount> fundAccounts = (List<FundAccount>) dzzfJdbcTemplate.query(sql, new ListResultSetExtractor(new FundAccount()), ecardNo);
		return CollectionUtils.isEmpty(fundAccounts) ? null : fundAccounts.get(0);
	}

	public CardInfo queryCardInfoByNo(String cardNo) {
		final String sql = "SELECT * FROM AC_CARDINFO A WHERE A.ECARDNO = ? ";
		@SuppressWarnings("unchecked")
		List<CardInfo> cardInfos = (List<CardInfo>) dzzfJdbcTemplate.query(sql, new ListResultSetExtractor(new CardInfo()), cardNo);
		return CollectionUtils.isEmpty(cardInfos) ? null : cardInfos.get(0);
	}


	public int updateCardInfoUserIdbyId(String userId, String cardInfoId) {
		final String sql = "UPDATE AC_CARDINFO A SET A.USERID=? WHERE A.CARDINFOID=?";
		return testJdbcTemplate.update(sql, userId, cardInfoId);
	}

	public int  queryVehicleNumByUserId(String vehicleId, String userId) {
		final String sql = "SELECT COUNT(1) FROM BS_VEHICLE WHERE ID=? AND USERID=? ";
		return testJdbcTemplate.queryForObject(sql, Integer.class, vehicleId, userId);
	}

	public int updateUploadFlag(String id, String tableName, int flag) {
		return testJdbcTemplate.update(String.format(UPDATE_USERINFO_UPLOAD, tableName), flag, id);
	}

	public  int updateVehicleFlag(String id, String userId, int flag){
		final String sql = "UPDATE BS_VEHICLE A SET A.UPLOADFLAG = ? WHERE A.ID=? AND A.USERID=?";
		return testJdbcTemplate.update(sql, flag, id, userId);
	}

	public int deleteFailureLog(String infoId, String type){
		final String sql = "DELETE FROM BS_UPLOADRSP A WHERE A.INFOID=? AND A.TYPE=?";
		return testJdbcTemplate.update(sql, infoId, type);
	}

	public int queryUploadFlagNum(String tableName, int flag) {
		final String sql = "SELECT COUNT(1) FROM %s WHERE UPLOADFLAG = ? ";
		return testJdbcTemplate.queryForObject(String.format(sql, tableName), Integer.class, flag);
	}

	public int queryFailureUploadNum(String tableName) {
		final String sql = "SELECT COUNT(1) FROM %s WHERE UPLOADFLAG=2";
		return testJdbcTemplate.queryForObject(String.format(sql, tableName), Integer.class);
	}

	public List<? extends AbstractSdtsRequest> queryFailureUploadInfos(ExecuteTable table, int startRow, int endRow) {
		final String sql = "SELECT * FROM (SELECT A.*, ROWNUM AS RM  FROM %s A WHERE UPLOADFLAG=2) WHERE RM >=? AND RM <= ? ";
		return testJdbcTemplate.query(String.format(sql, table.getTableName()), BeanPropertyRowMapper.newInstance(table.getRequestType()), startRow, endRow);
	}

	public int queryUploadRspNum(String infoId, String type) {
		final String sql = "SELECT COUNT(1) FROM BS_UPLOADRSP A WHERE A.INFOID=? AND A.TYPE=? ";
		return testJdbcTemplate.queryForObject(sql, Integer.class, infoId, type);
	}

	public int queryVehicleRspNum(String vehicleId, String userId){
		final String sql = "SELECT COUNT(1) FROM BS_VEHICLE A WHERE A.ID=? AND A.USERID=?";
		return testJdbcTemplate.queryForObject(sql, Integer.class, vehicleId, userId);
	}

	public Integer deleteUploadRsp(String infoId, String type) {
		final String sql = "DELETE FROM BS_UPLOADRSP A WHERE A.INFOID=? AND A.TYPE=? ";
		return testJdbcTemplate.update(sql, infoId, type);
	}

	public List<UploadRsp> queryFailureRequstJson(String type, int processNum) {
		final String sql =
				"SELECT *\n" +
						"  FROM (SELECT A.*, ROWNUM AS RM \n" +
						"          FROM BS_UPLOADRSP A\n" +
						"         WHERE A.TYPE = ? AND A.ERRORCODE = 704)\n" +
						" WHERE RM <= ?";
		return testJdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(UploadRsp.class), type, processNum);
	}

	public String queryUserDepartment(String userId){
		final String sql = "SELECT DEPARTMENT FROM KF_COMPANYINFO A WHERE A.ID=?";
		return dzzfJdbcTemplate.queryForObject(sql, String.class, userId);
	}

	public int updateBsUserDepartment(String id, String department){
		final String sql = "UPDATE BS_USER A SET A.DEPARTMENT = ? WHERE A.ID=? ";
		return testJdbcTemplate.update(sql, department, id);
	}

	public String generateUserId() {
		StringBuilder sqlBuilder = new StringBuilder();
		sqlBuilder.append("SELECT REPLACE('610101' || TO_CHAR(TRUNC(SYSDATE), 'YYMMDD') ||");
		sqlBuilder.append(" TO_CHAR(NVL(MAX(T.RN),0) + 1, '00000'),");
		sqlBuilder.append(" ' ', '') AS USERID");
		sqlBuilder.append("  FROM (SELECT K.ID, ROWNUM RN");
		sqlBuilder.append(" FROM (SELECT KU.ID");
		sqlBuilder.append(" FROM KF_USERINFO KU");
		sqlBuilder.append(" WHERE KU.REGISTEREDTIME >= TRUNC(SYSDATE)");
		sqlBuilder.append(" AND KU.REGISTEREDTIME < TRUNC(SYSDATE) + 1");
		sqlBuilder.append(" UNION ALL");
		sqlBuilder.append(" SELECT KC.ID RN");
		sqlBuilder.append(" FROM KF_COMPANYINFO KC");
		sqlBuilder.append(" WHERE KC.REGISTEREDTIME >= TRUNC(SYSDATE)");
		sqlBuilder.append(" AND KC.REGISTEREDTIME < TRUNC(SYSDATE) + 1) K) T");
		return this.dzzfJdbcTemplate.queryForObject(sqlBuilder.toString(),String.class);
	}

	public UserUploadRequest queryUserInfoById(String userId){
		final String sql = "SELECT * FROM BS_USER A WHERE A.ID=? ";
		List<UserUploadRequest> requests = testJdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(UserUploadRequest.class), userId);
		return CollectionUtils.isEmpty(requests) ? null : requests.get(0);
	}

	public static void main(String[] args) {
		final String sql =
				"SELECT *\n" +
						"  FROM (SELECT A.*, ROWNUM AS RM \n" +
						"          FROM BS_UPLOADRSP A\n" +
						"         WHERE A.TYPE = ? \n" +
						"           AND A.ERRORMESSAGE NOT LIKE '%发行方发行有卡%'\n" +
						"           AND A.ERRORMESSAGE NOT LIKE '%此户车辆信息已经上传%'\n" +
						"           AND A.ERRORMESSAGE NOT LIKE '%已发行有OBU%')\n" +
						" WHERE RM <= ?";
		System.out.println(sql);
	}

}
